﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_domain_dml_upsert]
	@TVP as flcr.DOMAIN_UDT READONLY
AS	

BEGIN TRY
  
	MERGE	[flcr].[DOMAIN] a
	USING	(SELECT	[DOMAIN_ID]	
				,[AUDIT_USER]
				,[AUDIT_DTTM]
				,[AUDIT_DTTM_TZ]
				,[AUDIT_MOD_USER]
				,[AUDIT_MOD_DTTM]
				,[AUDIT_MOD_DTTM_TZ]
				,[RLS_MASK]
				,[RLS_OWNER]
				,[BUILTIN]
				,[WRKFL_ITEM_ID]
				,[WRKFL_STATUS_ID]
				,[ACTIVE_FLG]
				,[SORT_ORDER]
				,[DOMAIN_NAME]
				,[DOMAIN_DESC]
				,[PARENT_ID]
			   FROM	@TVP) B
	    ON	A.[DOMAIN_ID] = B.[DOMAIN_ID]
	  WHEN NOT MATCHED THEN	  	  
		INSERT ([AUDIT_USER]
					,[AUDIT_DTTM]
					,[AUDIT_DTTM_TZ]
					,[AUDIT_MOD_USER]
					,[AUDIT_MOD_DTTM]
					,[AUDIT_MOD_DTTM_TZ]
					,[RLS_MASK]
					,[RLS_OWNER]
					,[BUILTIN]
					,[WRKFL_ITEM_ID]
					,[WRKFL_STATUS_ID]
					,[ACTIVE_FLG]
					,[SORT_ORDER]
					,[DOMAIN_NAME]
					,[DOMAIN_DESC]
					,[PARENT_ID])
		VALUES (B.[AUDIT_USER]
					,B.[AUDIT_DTTM]
					,B.[AUDIT_DTTM_TZ]
					,B.[AUDIT_MOD_USER]
					,B.[AUDIT_MOD_DTTM]
					,B.[AUDIT_MOD_DTTM_TZ]
					,B.[RLS_MASK]
					,B.[RLS_OWNER]
					,B.[BUILTIN]
					,B.[WRKFL_ITEM_ID]
					,B.[WRKFL_STATUS_ID]
					,B.[ACTIVE_FLG]
					,B.[SORT_ORDER]
					,B.[DOMAIN_NAME]
					,B.[DOMAIN_DESC]
					,B.[PARENT_ID])
		OUTPUT inserted.*;	
				
END TRY
BEGIN CATCH
	EXEC [flcr].[utl_GetDBError];
END CATCH